package com.gege.homework10;

import com.gege.homework10.model.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 使用事务，PrepareStatement 方式，批处理方式
 *
 * @author gechenpeng
 * @date 2021-04-20
 */
public class JdbcWithTxDemo {

    private static final String url = "jdbc:mysql://localhost:3306/gogo?useUnicode=true&characterEncoding=utf-8&useSSL=false";

    private static final String username = "xiaogege";

    private static final String password = "imissyou";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= 5; i++) {
            userList.add(User.builder()
                    .id("2021042117" + i)
                    .nickname("name" + i)
                    .phoneNumber("1801234567" + i)
                    .build());
        }

        batchAddUser(userList);
    }

    public static void batchAddUser(List<User> users) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);

            String sql = "INSERT INTO user (id, nickname, phone_number) VALUES (?, ?, ?)";
            statement = connection.prepareStatement(sql);

            for (User user : users) {
                statement.setString(1, user.getId());
                statement.setString(2, user.getNickname());
                statement.setString(3, user.getPhoneNumber());

                statement.addBatch();
            }

            statement.executeBatch();

            connection.commit();
        } finally {
            close(null, statement, connection);
        }
    }

    public static void addUser(User user) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);

            String sql = "INSERT INTO user (id, nickname, phone_number) VALUES (?, ?, ?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, user.getId());
            statement.setString(2, user.getNickname());
            statement.setString(3, user.getPhoneNumber());

            statement.executeUpdate();

            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            close(null, statement, connection);
        }
    }

    public static void updateUser(User user) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);

            String sql = "UPDATE user SET phone_number = ? WHERE nickname = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, user.getPhoneNumber());
            statement.setString(2, user.getNickname());

            statement.executeUpdate();

            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            close(null, statement, connection);
        }
    }

    public static int deleteUser(String nickname) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            String sql = "DELETE FROM user WHERE nickname = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, nickname);
            return statement.executeUpdate();
        } finally {
            close(null, statement, connection);
        }
    }

    private static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (null != resultSet) {
                resultSet.close();
            }

            if (null != statement) {
                statement.close();
            }

            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static List<User> queryUsers() throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "SELECT * FROM user";
            resultSet = statement.executeQuery(sql);
            List<User> userList = new ArrayList<>();
            while (resultSet.next()) {
                userList.add(User.builder()
                        .id(resultSet.getString("id"))
                        .nickname(resultSet.getString("nickname"))
                        .phoneNumber(resultSet.getString("phone_number"))
                        .build());
            }
            return userList;
        } finally {
            close(resultSet, statement, connection);
        }
    }

    private static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
}
